﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HsTec.DigitalPlatform.DataAccess.Search;

namespace HsTec.DigitalPlatform.DataAccess {
    public class PlayerItemsInfoRepository {
        DigitalPlatformEntities db = new DigitalPlatformEntities();

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="playeritemsinfo"></param>
        public void Add(PlayerItemsInfo playeritemsinfo) {
            db.AddToPlayerItemsInfos(playeritemsinfo);
            db.SaveChanges();
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="playeritemsinfo"></param>
        public void Delete(PlayerItemsInfo playeritemsinfo) {
            db.DeleteObject(playeritemsinfo);
            db.SaveChanges();
        }

        /// <summary>
        /// 保存
        /// </summary>
        public void Save() {
            db.SaveChanges();
        }

        /// <summary>
        /// 根据ID查询实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public PlayerItemsInfo GetPlayerItemsInfoByID(int id) {
            return db.PlayerItemsInfos.FirstOrDefault<PlayerItemsInfo>(d => d.itemsid == id);
        }

        /// <summary>
        /// 返回所有实体
        /// </summary>
        /// <returns></returns>
        public IQueryable<PlayerItemsInfo> FindALLPlayerItemsInfo() {
            return db.PlayerItemsInfos;
        }

        public IQueryable<PlayerItemsInfo> PageInfo(int page, int rp, string sortname, string sortorder) {
            return db.PlayerItemsInfos.OrderBy("it." + sortname + " " + sortorder).Skip((page - 1) * rp).Take(rp);
        }

        public int GetAllCount() {
            return db.PlayerItemsInfos.Count();
        }

        public List<ViPlayerItmesInfo> GetItemsInfos(int playerid) {
            return db.ViPlayerItmesInfos.Where(t => t.playerid == playerid).ToList<ViPlayerItmesInfo>();
        }

        public void DeleteByPlayer(int playerid) {
            List<PlayerItemsInfo> list = db.PlayerItemsInfos.Where(t => t.playerid == playerid).ToList<PlayerItemsInfo>();
            foreach (PlayerItemsInfo pii in list) {
                db.DeleteObject(pii);
            }
        }

        /// <summary>
        /// 查询导出数据
        /// </summary>
        /// <param name="qtxt"></param>
        /// <param name="qslt"></param>
        /// <returns></returns>
        public string GetQueryInfo(string qtxt, string qslt, int type) {
            StringBuilder sbTable = new StringBuilder();
            sbTable.AppendLine("<table border='1px'>");
            switch (qslt) {
                case "1":
                    //按班级获取信息
                    //1.取出所有的班级信息（有报名信息的）
                    var classlist = (from c in db.ViPlayerItmesInfos
                                     select c.classname).Distinct();
                    if (!String.IsNullOrEmpty(qtxt))
                        classlist = classlist.Where(t => t.Contains(qtxt));
                    foreach (string classname in classlist) {
                        //2.分别获取每个班的报名人
                        var qlist = (from c in db.ViPlayerItmesInfos
                                     where c.classname == classname
                                     select c.playeno);
                        List<string> playerlist = qlist.Distinct().ToList<string>();
                        sbTable.AppendLine("<tr>");
                        sbTable.AppendLine("<td bgcolor='#00FF00' colspan='10'>班级：" + classname + "&nbsp;&nbsp;&nbsp;&nbsp;报名运动员共" + playerlist.Count + "人</td>");
                        sbTable.AppendLine("</tr>");
                        sbTable.AppendLine("<tr>");
                        sbTable.AppendLine("<td>编号</td>");
                        sbTable.AppendLine("<td>学号</td>");
                        sbTable.AppendLine("<td>姓名</td>");
                        sbTable.AppendLine("<td>性别</td>");
                        sbTable.AppendLine("<td>专业</td>");
                        sbTable.AppendLine("<td>项目1</td>");
                        sbTable.AppendLine("<td>项目2</td>");
                        sbTable.AppendLine("<td>项目3</td>");
                        sbTable.AppendLine("<td>项目4</td>");
                        sbTable.AppendLine("<td>项目5</td>");
                        sbTable.AppendLine("</tr>");
                        foreach (string playerid in playerlist) {
                            //3.获取报名人的相关项目
                            List<ViPlayerItmesInfo> q = (from c in db.ViPlayerItmesInfos
                                                         where c.playeno == playerid
                                                         select c).ToList<ViPlayerItmesInfo>();
                            if (q.Count > 0) {
                                ViPlayerItmesInfo player = q[0];
                                sbTable.AppendLine("<tr>");
                                if (type == 1) {
                                    sbTable.AppendLine("<td>" + player.playnumber + "</td>");
                                }
                                else {
                                    sbTable.AppendLine("<td>" + "'" + player.playnumber + "</td>");
                                }
                                sbTable.AppendLine("<td>" + player.playeno + "</td>");
                                sbTable.AppendLine("<td>" + player.playername + "</td>");
                                sbTable.AppendLine("<td>" + player.sex + "</td>");
                                sbTable.AppendLine("<td>" + player.spename + "</td>");
                                foreach (ViPlayerItmesInfo p in q) {
                                    sbTable.AppendLine("<td>" + p.itemname + "</td>");
                                }
                                for (int i = q.Count; i < 5; i++) {
                                    sbTable.AppendLine("<td>&nbsp;</td>");
                                }
                                sbTable.AppendLine("</tr>");
                            }
                        }
                    }
                    break;
                case "2":
                    //按项目获取信息
                    //1.取出所有的项目信息

                    var itemslist = (from c in db.ItemInfos
                                     select c.itemname).Distinct();
                    if (!String.IsNullOrEmpty(qtxt))
                        itemslist = itemslist.Where(t => t.Contains(qtxt));
                    foreach (string itemsname in itemslist) {
                        //2.分别获取每个项目的报名人
                        var ilist = (from c in db.ViPlayerItmesInfos
                                     where c.itemname == itemsname
                                     select c.playeno);
                        List<string> playeritemslist = ilist.Distinct().ToList<string>();
                        sbTable.AppendLine("<tr>");
                        sbTable.AppendLine("<td bgcolor='#00FF00' colspan='6'>项目：" + itemsname + "&nbsp;&nbsp;&nbsp;&nbsp;报名运动员共" + ilist.Count() + "人</td>");
                        sbTable.AppendLine("</tr>");
                        sbTable.AppendLine("<tr>");
                        sbTable.AppendLine("<td>编号</td>");
                        sbTable.AppendLine("<td>学号</td>");
                        sbTable.AppendLine("<td>姓名</td>");
                        sbTable.AppendLine("<td>性别</td>");
                        sbTable.AppendLine("<td>专业</td>");
                        sbTable.AppendLine("<td>班级</td>");
                        sbTable.AppendLine("</tr>");

                        foreach (string playerid in playeritemslist) {
                            List<ViPlayerItmesInfo> q = (from c in db.ViPlayerItmesInfos
                                                         where c.playeno == playerid
                                                         select c).ToList<ViPlayerItmesInfo>();
                            if (q.Count > 0) {
                                ViPlayerItmesInfo player = q[0];
                                sbTable.AppendLine("<tr>");
                                if (type == 1) {
                                    sbTable.AppendLine("<td>" + player.playnumber + "</td>");
                                }
                                else {
                                    sbTable.AppendLine("<td>" + "'" + player.playnumber + "</td>");
                                }
                                sbTable.AppendLine("<td>" + player.playeno + "</td>");
                                sbTable.AppendLine("<td>" + player.playername + "</td>");
                                sbTable.AppendLine("<td>" + player.sex + "</td>");
                                sbTable.AppendLine("<td>" + player.spename + "</td>");
                                sbTable.AppendLine("<td>" + player.classname + "</td>");
                                sbTable.AppendLine("</tr>");
                            }
                        }
                    }
                    break;
                case "3":
                    //按运动员获取信息
                    break;
            }
            sbTable.AppendLine("</table>");
            return sbTable.ToString();
        }
    }
}
